package dqcs;

//import java.sql.Driver;
//import java.sql.DriverManager;
//import java.sql.Statement;
//import java.sql.ResultSet;
//import java.sql.Connection;
import java.io.FileOutputStream;
import java.io.File;
import java.text.SimpleDateFormat;
//import java.sql.PreparedStatement;
import java.sql.*;
import java.io.ByteArrayInputStream;

public class BatchDownloadDqcsAttach {
    public static boolean gblnDebug = false;

    public BatchDownloadDqcsAttach() {
    }

    public static void main(String[] args) {
        BatchDownloadDqcsAttach batchdownloaddqcsattach = new
                BatchDownloadDqcsAttach();
        Connection conDQCS = null;
        ResultSet lrstDqcsID = null;
        ResultSet lrstDqcsAtt = null;
        java.sql.Statement lstmtExecuteSQL = null;
        String lstrFileID = null;
        int lintDownCount = 0;
        int lintDownMaxCount = 300;
        String lstrStartTime = null;
        String lstrResult = null;
        byte[] lbteData = null;
        StringBuffer lstbOutPath=new StringBuffer("D:/DQCS_ATTACH/");
        StringBuffer lstbOutFileName=new StringBuffer();

        try {
            lstrStartTime = (new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).
                            format((new java.util.Date())).toString();

            if (gblnDebug) {
                System.out.println("new connection...");
            }
            conDQCS = batchdownloaddqcsattach.getConn(args[0], "dqcs");
            //@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
            batchdownloaddqcsattach.expandPN2ID(conDQCS); //for transfer PN to ID
            //@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
            if (gblnDebug) {
                System.out.println("create statement...");
            }

            lstmtExecuteSQL = conDQCS.createStatement();

            if (args != null && args.length == 2 ){
                if (args[1] != null && !args[1].equals("")) {
                    lintDownMaxCount = Integer.parseInt(args[1]);
                } else {
                    lintDownMaxCount = Integer.parseInt(conDQCS.createStatement().
                                                        executeQuery(
                            "select count(id) as count_id from dqcs_down_id").
                                                        getString("count_id"));
                }
            }
            if (gblnDebug) {
                System.out.println(
                        "select id,pn,model,empno from dqcs_down_id where rownum<=" +
                        String.valueOf(lintDownMaxCount).trim() +
                        " order by empno");
            }
            lrstDqcsID = conDQCS.createStatement().executeQuery(
                    "select id,pn,model,empno,doc_kind from dqcs_down_id where rownum<=" +
                    String.valueOf(lintDownMaxCount).trim() +
                    " order by empno");

            //new File("D:/DQCS_ATTACH/" + (new SimpleDateFormat("yyyyMMdd")).format((new java.util.Date())).toString()).mkdir();

            while (lrstDqcsID.next()) {
                try{
                    lintDownCount++;

                    lstrFileID = lrstDqcsID.getString("id");

                    if (gblnDebug) {
                        System.out.println(
                                "select filedata,filename from dqcs_attach where id='" +
                                lstrFileID + "'");
                    }
                    lrstDqcsAtt = conDQCS.createStatement().executeQuery(
                            "select filedata,filename from dqcs_attach where id='" +
                            lstrFileID + "'");

                    if(!lrstDqcsAtt.next()){
                        continue;
                    }

                    lbteData = lrstDqcsAtt.getBytes("filedata");

                    lstbOutPath.append((new SimpleDateFormat("yyyyMMdd")).
                                       format((new java.util.Date())).toString() +
                                       "/" +
                                       lrstDqcsID.getString("model").trim() + "/" + lrstDqcsID.getString("doc_kind").trim());

                    new File(lstbOutPath.toString().trim()).mkdirs();

                    if (gblnDebug) {
                        System.out.println("ouput file....");
                    }

                    lstbOutFileName.append(lstbOutPath);
                    lstbOutFileName.append("/" + lrstDqcsID.getString("pn").trim() +
                                           "-" +
                                           lrstDqcsAtt.getString("filename").trim());

                    System.out.println(lintDownCount + "==>" + lstrFileID +
                                       "==>" + lstbOutFileName.toString());

                    if (!batchdownloaddqcsattach.checkFile(lstbOutFileName.
                            toString().trim())) {
                        batchdownloaddqcsattach.outputFile(lstbOutFileName.
                                toString().trim(),
                                lbteData);
                    }

                    lstbOutPath.delete(0, lstbOutPath.length());
                    lstbOutPath.append("D:/DQCS_ATTACH/");
                    lstbOutFileName.delete(0, lstbOutFileName.length());

                    if (gblnDebug) {
                        System.out.println("delete result==>" +
                                           "delete dqcs_down_id where id='" +
                                           lstrFileID + "' and empno='" +
                                           lrstDqcsID.getString("empno") + "'"
                                );
                    }
                    lstmtExecuteSQL.execute(
                            "delete dqcs_down_id where id='" +
                            lstrFileID + "' and empno='" +
                            lrstDqcsID.getString("empno") + "'");

                    lbteData = null;

                    if (lrstDqcsAtt != null) {
                        try {
                            lrstDqcsAtt.getStatement().close();
                            lrstDqcsAtt.close();
                            lrstDqcsAtt = null;
                        } catch (Exception e) {
                            System.out.println(
                                    "can't close lrstDqcsAtt resultset(SendAttachToMail.java(main))==> " +
                                    e.toString());
                        }

                    }

                    if (lintDownCount == lintDownMaxCount) {
                        break;
                    }
                }
                catch(Exception se){
                    se.printStackTrace();
                    lstbOutPath.delete(0, lstbOutPath.length());
                    lstbOutPath.append("D:/DQCS_ATTACH/");
                    lstbOutFileName.delete(0, lstbOutFileName.length());
                }
            }

            lstrResult = "success";

            if (gblnDebug) {
                System.out.println("insert result==>" +
                                   "insert into dqcs_down_att_log values(" +
                                   String.valueOf(lintDownMaxCount).trim() +
                                   ",to_date('" + lstrStartTime +
                                   "','yyyy/MM/dd hh24:mi:ss'),sysdate,'" +
                                   lstrResult + "')"
                        );
            }

            lstmtExecuteSQL.execute("insert into dqcs_down_att_log values(" +
                                    String.valueOf(lintDownMaxCount).trim() +
                                    ",to_date('" + lstrStartTime +
                                    "','yyyy/MM/dd hh24:mi:ss'),sysdate,'" +
                                    lstrResult + "')");
        } catch (Exception e) {
            e.printStackTrace();
            lstrResult = "error";
        } finally {
            if (lstmtExecuteSQL != null) {
                try {
                    lstmtExecuteSQL.close();
                    lstmtExecuteSQL = null;
                } catch (Exception e) {
                    System.out.println(
                            "can't close lstmtExecuteSQL resultset(BatchDownloadDqcsAttach.java(main))==> " +
                            e.toString());
                }
            }

            if (lrstDqcsID != null) {
                try {
                    lrstDqcsID.getStatement().close();
                    lrstDqcsID.close();
                    lrstDqcsID = null;
                } catch (Exception e) {
                    System.out.println(
                            "can't close lrstDqcsID resultset(SendAttachToMail.java(main))==> " +
                            e.toString());
                }
            }

            if (lrstDqcsAtt != null) {
                try {
                    lrstDqcsAtt.getStatement().close();
                    lrstDqcsAtt.close();
                    lrstDqcsAtt = null;
                } catch (Exception e) {
                    System.out.println(
                            "can't close lrstDqcsAtt resultset(SendAttachToMail.java(main))==> " +
                            e.toString());
                }
            }
            try {
                if (conDQCS != null) {
                    conDQCS.rollback();
                    conDQCS.close();
                    conDQCS = null;
                }
            } catch (Exception e) {
                System.out.println(
                        "main() can't close resultset and statement in BatchDownloadDqcsAttach.java==>" +
                        e.toString());
            }
        }
    }

    public Connection getConn(String strServiceName, String strUid) throws
            Exception {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rst = null;
        String strSQL = "";
        boolean blnDebug = false;

        //JDBC\uFFFD嚙踝蕭瘜�蕭\uFFFD嚙瞎ark
        try {
            //\uFFFD\uFFFD\uFFFD\uFFFD嚙踝蕭\uFFFD嚙踝蕭甈莎蕭\uFFFD嚙賢�\uFFFD嚙踝蕭\uFFFD\uFFFD嚙賢�\uFFFD\uFFFD嚙磅ervice_name\uFFFD\uFFFD\uFFFD user
            if (strServiceName == null || strServiceName.trim().equals("") ||
                strServiceName.equals("null")) {
                if (blnDebug) {
                    System.out.println("Connect to DB error strServiceName==>" +
                                       strServiceName);
                }
                return null;
            }

            if (strUid == null || strUid.trim().equals("") ||
                strUid.equals("null")) {
                if (blnDebug) {
                    System.out.println("DB user error strUid==>" + strUid);
                }
                return null;
            }

            String jdbcUrl = new String();
            String jdbcDriver = new String();
            String jdbcUid = new String();
            String jdbcPwd = new String();

            //\uFFFD嚙踝蕭\uFFFD嚙踝蕭\uFFFD\uFFFD嚙踝蕭\uFFFD\uFFFD\uFFFD嚙踝蕭鞈�蕭\uFFFD嚙賢澈嚗�蕭\uFFFD嚙踝蕭\uFFFD\uFFFD\uFFFD\uFFFD嚙篌dbc\uFFFD\uFFFD\uFFFD\uFFFD嚙踝蕭\uFFFD嚙踝蕭\uFFFD\uFFFD嚙踝蕭\uFFFD嚙賣�\uFFFD嚙踝蕭\uFFFD嚙踝蕭kmdb銝�蕭\uFFFD嚙篌dbc_conn table
            jdbcUrl = "jdbc:oracle:thin:@srvpidkmdb:1521:pidkmdb";
            jdbcDriver = "oracle.jdbc.driver.OracleDriver";
            jdbcUid = "kmdb";
            jdbcPwd = "kmpid";

            if (strUid == null || strUid.trim().equals("") ||
                strUid.equals("null")) {
                if (blnDebug) {
                    System.out.println("DB user error strUid==>" + strUid);
                }
                return null;
            }

            if (strServiceName == null || strServiceName.trim().equals("") ||
                strServiceName.equals("null")) {
                if (blnDebug) {
                    System.out.println("DB user error strServiceName=>" +
                                       strServiceName);
                }
                return null;
            }

            //Class.forName(jdbcDriver);
            DriverManager.registerDriver((Driver) (Class.forName(jdbcDriver).
                    newInstance()));
            conn = DriverManager.getConnection(jdbcUrl, jdbcUid, jdbcPwd);

            stmt = conn.createStatement();

            //\uFFFD\uFFFD嚙賢�甈莎蕭\uFFFD\uFFFD\uFFFD嚙踝蕭\uFFFD\uFFFD嚙範b\uFFFD嚙踝蕭\uFFFD\uFFFD嚙篌dbc\uFFFD\uFFFD嚙賣�
            strSQL = "select * from jdbc_conn where upper(service_name)='" +
                     strServiceName.toUpperCase() + "' and upper(user_id)='" +
                     strUid.toUpperCase() + "'";

            //if(strHost.toUpperCase().equals("PIS1"))
            //  strSQL +=" and flag='N'";

            rst = stmt.executeQuery(strSQL);

            if (blnDebug) {
                System.out.println("strSQL==>" + strSQL);
            }
            if (blnDebug) {
                System.out.println("rst is null==>" + (rst == null));
            }

            if (rst == null || !rst.next()) {
                if (blnDebug) {
                    System.out.println("can not get jdbc_conn data...");
                }
                return null;
            }

            jdbcUrl = rst.getString("JDBC_URL");
            jdbcDriver = rst.getString("JDBC_DRIVER");
            jdbcUid = rst.getString("USER_ID");
            jdbcPwd = rst.getString("USER_PASS");

            System.out.println("jdbcDriver==>" + jdbcDriver);
            System.out.println("jdbcUrl==>" + jdbcUrl);
            System.out.println("jdbcUid==>" + jdbcUid);
            System.out.println("jdbcPwd==>" + jdbcPwd);

            DriverManager.registerDriver((Driver) (Class.forName(jdbcDriver).
                    newInstance()));
            conn = DriverManager.getConnection(jdbcUrl, jdbcUid, jdbcPwd);

            if(conn!=null){
                System.out.println("get conn ok==>" + !conn.isClosed());
            }
            return conn;
        } catch (Exception e) {
            if (gblnDebug) {
                e.printStackTrace();
            }
            throw new Exception(e);
        } finally {
            try {
                if (rst != null) {
                    rst.close();
                    rst.getStatement().close();
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
            } catch (Exception e) {
                System.out.println(
                        "getconn() can't close resultset and statement in SendAttachToMail.java==>" +
                        e.toString());
            }
        }
    }

    private void outputFile(String pstrFileName, byte[] pbteData) throws
            Exception {
        FileOutputStream fosFile = null;

        try {
            fosFile = new FileOutputStream(pstrFileName);
            fosFile.write(pbteData);
            fosFile.close();
            fosFile = null;
        } catch (Exception e) {
            if (gblnDebug) {
                e.printStackTrace();
            }
            throw new Exception(e);
        } finally {
            try {
                if (fosFile != null) {
                    fosFile.close();
                    fosFile = null;
                }
            } catch (Exception e) {
                System.out.println(
                        "outputFile() cant close fosFile object in SendAttachToMail.java==>" +
                        e.toString());
            }
        }
    }

    private boolean checkFile(String pstrFileName) throws Exception {
        return (new File(pstrFileName)).exists();
    }

    private void expandPN2ID(Connection conDQCS) throws Exception {
        boolean blnDebug=false;
        Statement stmt=null;
        ResultSet rset=null;
        Statement findIDStmt=null;
        ResultSet findIDRset=null;
        PreparedStatement preStmt = null;
        String inSql = "";
        String lstrID=null;
        String lstrPN=null;
        String lstrModel=null;
        String lstrEmpno=null;

        int upCount=0;

        stmt = conDQCS.createStatement();
        rset=stmt.executeQuery("select * from dqcs_down_pn where expand='N'");
        findIDStmt = conDQCS.createStatement();
        try{
            for(int j=11;j<=12;j++){ //���閬��R11,R12��indIDRset����單�暻澆�sql,so...loop is fast :PPP...
                while (rset.next()) {
                    findIDRset = findIDStmt.executeQuery(
                            "select id from dqcs_attach where flow_num in ("
                            +
                            "select flow_num from dqcs_main where doc_no like 'R" + j + "-" +
                            (String) rset.getString("pn")
                            + "%' and doc_status='1') and mark='0'");
                    // + "%' and doc_status='1') and mark='0' and id not in(select id from dqcs_down_id)");
                    /* findIDRset = findIDStmt.executeQuery(
                         "select id from dqcs_attach where flow_num in ("
                     + "select flow_num from dqcs_main where doc_no like 'R11-" +
                         "302122004401G"
                         + "%' and doc_status='1') and mark='0' and id not in(select id from dqcs_down_id)");*/
                    /*
                     System.out.println("select id from dqcs_attach where flow_num in ("
                     + "select flow_num from dqcs_main where doc_no like 'R11-" +
                                    (String) rset.getString("pn")
                                    + "%' and doc_status='1') and mark='0' and id not in(select id from dqcs_down_id)");
                     */
                    while (findIDRset.next()) {

                        lstrID=(String) findIDRset.getString("ID");
                        lstrPN=(String) rset.getString("PN");
                        lstrModel=(String) rset.getString("MODEL");
                        lstrEmpno=(String) rset.getString("EMPNO");

                        conDQCS.createStatement().execute("delete from dqcs_down_id where \"ID\"='" + lstrID + "' and pn='" + lstrPN + "' and model='" + lstrModel + "' and empno='" + lstrEmpno + "'");
                        inSql = ""; //clean old data
                        inSql +=
                                "INSERT into dqcs_down_id (\"ID\",PN,MODEL,EMPNO,TRANSFER,DOC_KIND) "
                                + " VALUES (?,?,?,?,?,?)";
                        if (blnDebug) {
                            System.out.println("insql" + inSql);
                        }
                        java.util.Vector vtrData = new java.util.Vector();
                        vtrData.add(lstrID);
                        vtrData.add(lstrPN);
                        if (blnDebug) {
                            System.out.println("(String) rset.getString(PN)" +
                                               lstrPN);
                        }
                        vtrData.add(lstrModel);

                        vtrData.add(lstrEmpno);
                        vtrData.add("N");
                        vtrData.add("R" + String.valueOf(j).trim());
                        preStmt = conDQCS.prepareStatement(inSql);
                        if (vtrData != null) {
                            for (int i = 0; i < vtrData.size(); i++) {
                                Object paramObj = vtrData.elementAt(i);
                                if (paramObj == null)
                                    preStmt.setNull(i + 1, java.sql.Types.NULL);
                                else if (paramObj instanceof String)
                                    preStmt.setString(i + 1,
                                            (String) paramObj);
                                else if (paramObj instanceof Timestamp) {
                                    preStmt.setTimestamp(i +
                                            1, (Timestamp) paramObj);
                                } else if (paramObj instanceof Integer)
                                    preStmt.setInt(i + 1,
                                            ((Integer) paramObj).intValue());
                                else if (paramObj instanceof Float)
                                    preStmt.setFloat(i + 1,
                                            ((Float) paramObj).floatValue());
                                else if (paramObj instanceof byte[]) {
                                    byte[] bdata = (byte[]) paramObj;
                                    ByteArrayInputStream bin = new
                                            ByteArrayInputStream(bdata);
                                    preStmt.setBinaryStream(i + 1, bin,
                                            bdata.length);
                                }
                            }
                        }

                        upCount = preStmt.executeUpdate(); //excute here~~~return int
                        if (upCount == 1) {
                            System.out.println("Insert one successful !!!");
                        }
                        preStmt.close(); //�������舫���ut of membery

                    }
                    //���銋��敹��UPDATE銝餅�
                    if (upCount == 1) {
                        preStmt = conDQCS.prepareStatement(
                                "update dqcs_down_pn set expand='Y' where pn='" +
                                (String) rset.getString("PN") + "' and empno='" +
                                (String) rset.getString("EMPNO") +
                                "' and model='" +
                                (String) rset.getString("MODEL") + "'");
                        int up2 = preStmt.executeUpdate(); //excute here~~~return int
                        if (up2 == 1) {
                            //System.out.println("update fail ! but it's OK!! never mind!!!");
                            System.out.println("update successful!");
                        }
                    }
                    if (preStmt != null)
                        preStmt.close();

                    upCount = 0;
                }
            }
        }catch(Exception e){
          e.printStackTrace();
        }finally{

          if (findIDRset != null) {
            try {
              findIDRset.close();
            }
            catch (Exception e) {
              System.out.println(this.toString().substring(this.toString().
                  lastIndexOf('.') + 1, this.toString().lastIndexOf('@')) +
                                 ".java" + " (getFileName) Exception --> " +
                                 e.toString());
            }
          }
          if (rset != null) {
            try {
              rset.close();
            }
            catch (Exception e) {
              System.out.println(this.toString().substring(this.toString().
                  lastIndexOf('.') + 1, this.toString().lastIndexOf('@')) +
                                 ".java" + " (getFileName) Exception --> " +
                                 e.toString());
            }
          }
          if (stmt != null) {
            try {
              stmt.close();
            }
            catch (Exception e) {
              System.out.println(this.toString().substring(this.toString().
                  lastIndexOf('.') + 1, this.toString().lastIndexOf('@')) +
                                 ".java" + " (getFileName) Exception --> " +
                                 e.toString());
            }
          }
          if (preStmt != null) {
            try {
              preStmt.close();
            }
            catch (Exception e) {
              System.out.println(this.toString().substring(this.toString().
                  lastIndexOf('.') + 1, this.toString().lastIndexOf('@')) +
                                 ".java" + " (getFileName) Exception --> " +
                                 e.toString());
            }
          }
          /*
          if (conDQCS != null) {
            try {
              conDQCS.close();
              if (blnDebug) {
                System.out.println("connection closed!!");
              }
            }
            catch (Exception e) {
              System.out.println(this.toString().substring(this.toString().
                  lastIndexOf('.') + 1, this.toString().lastIndexOf('@')) +
                                 ".java" + " (getFileName) Exception --> " +
                                 e.toString());
            }
          }*/
        }
    }
}
